﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Portal.Language;
using Portal.Environment;

namespace Portal.CDSP
{
    public class NewsDetailBLL
    {
        public string pc = "vvvxxx!@#$abc*()";
        public NewsDetailCls CreateModel(string ActiveLanguage, string ShortNewsId)
        {
            int UseEncypt = Environments.UseEncypt;
            if (Environments.UseXmlData)
            {
                string LoadFile = Environments.XmlFolder + "\\News\\" + ShortNewsId + ".html";
                if (new System.IO.FileInfo(LoadFile).Exists == false)
                {
                    return null;
                }
                else
                {
                    DataSet ds = new DataSet();
                    ds.ReadXml(LoadFile);
                    DataTable dtResult = ds.Tables[0];

                    NewsDetailCls NewsCls = new NewsDetailCls();
                    NewsCls.NewsId = (string)dtResult.Rows[0]["NewsId"];
                    NewsCls.belongSite = (string)dtResult.Rows[0]["belongSite"];
                    try
                    {
                        NewsCls.Subject = (string)dtResult.Rows[0][ActiveLanguage + "Subject"];
                        if (UseEncypt == 1)
                        {
                            NewsCls.Subject = (string)dtResult.Rows[0][ActiveLanguage + "EcryptSubject"];
                            NewsCls.Subject = new RSACSPCls().Decrypt(pc, NewsCls.Subject);
                        }
                    }
                    catch { }
                    try
                    {
                        NewsCls.Source = (string)dtResult.Rows[0]["Source"];
                    }
                    catch { }

                    try
                    {
                        NewsCls.SmallImage = (string)dtResult.Rows[0]["SmallImage"];
                    }
                    catch { }

                    try
                    {
                        NewsCls.UploadFileName = (string)dtResult.Rows[0]["UploadFileName"];
                    }
                    catch { }
                    try
                    {
                        NewsCls.ImageUrl = (string)dtResult.Rows[0]["ImageUrl"];
                    }
                    catch { }
                    try
                    {
                        NewsCls.ShortContent = (string)dtResult.Rows[0][ActiveLanguage + "ShortContent"];
                        if (UseEncypt == 1)
                        {
                            NewsCls.ShortContent = (string)dtResult.Rows[0][ActiveLanguage + "EcryptShortContent"];
                            NewsCls.ShortContent = new RSACSPCls().Decrypt(pc, NewsCls.ShortContent);
                        }
                    }
                    catch { }
                    try
                    {
                        NewsCls.Body = (string)dtResult.Rows[0][ActiveLanguage + "Body"];

                        if (UseEncypt == 1)
                        {
                            NewsCls.Body = (string)dtResult.Rows[0][ActiveLanguage + "EcryptBody"];
                            NewsCls.Body = new RSACSPCls().Decrypt(pc, NewsCls.Body);
                        }
                    }
                    catch { }
                    try
                    {
                        NewsCls.Owner = (string)dtResult.Rows[0]["Owner"];
                    }
                    catch { }
                    NewsCls.GroupNewsItemId = (string)dtResult.Rows[0]["fk_GroupNewsItem_Id"];
                    NewsCls.GroupNewsItemName = (string)dtResult.Rows[0]["fk_GroupNewsItem_Id"];
                    try
                    {
                        NewsCls.EntryDate = DateTime.Parse(dtResult.Rows[0]["EntryDate"].ToString());
                    }
                    catch { }
                    try
                    {
                        NewsCls.TotalCount = (int)dtResult.Rows[0]["TotalView"];
                    }
                    catch { }

                    try
                    {
                        NewsCls.DocumentContent = new byte[dtResult.Rows[0]["DocumentContent"].ToString().Length];
                    }

                    catch { NewsCls.DocumentContent = null; }

                    return NewsCls;
                }
            }
            else
            {
                string Query = "select NewsId,belongSite,Source=IsNull(Source,''),Subject=IsNull([" + ActiveLanguage + "Subject],''),ShortContent=[" + ActiveLanguage + "ShortContent],Body=[" + ActiveLanguage + "Body],EntryDate,fk_GroupNews_Id,fk_GroupNewsItem_Id,WorkflowStatus,Owner,ShortId,OriginUrl,OriginId,TotalView,ImageUrl,SmallImage,UploadFileName,DocumentContent,EmbVideo,EmbAudio from TableNews where ShortId=@ShortId";
                if (UseEncypt == 1)
                {
                    Query = "select NewsId,belongSite,UploadFileName=IsNull(UploadFileName,''),Source=IsNull(Source,''),Subject=IsNull([" + ActiveLanguage + "EcryptSubject],''),ShortContent=[" + ActiveLanguage + "EcryptShortContent],Body=[" + ActiveLanguage + "EcryptBody],EntryDate,fk_GroupNews_Id,fk_GroupNewsItem_Id,(select GroupNewsItemName from TableGroupNewsItem where GroupNewsItemId=TableNews.fk_GroupNewsItem_Id) TableGroupNewsItem,WorkflowStatus,Owner,ShortId,OriginUrl,OriginId,TotalView,ImageUrl,SmallImage from TableNews where ShortId=@ShortId";
                }
                SqlConnection sqlConnection = new SqlConnection(Environments.PublicConnectionString);
                sqlConnection.Open();
                SqlCommand sqlCommand = sqlConnection.CreateCommand();
                sqlCommand.CommandText = Query;
                sqlCommand.Parameters.Add("@ShortId", SqlDbType.VarChar);
                sqlCommand.Parameters["@ShortId"].Value = ShortNewsId;
                DataTable dtResult = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                da.Fill(dtResult);
                if (dtResult.Rows.Count == 0)
                {
                    return null;
                }
                else
                {
                    NewsDetailCls NewsCls = new NewsDetailCls();
                    NewsCls.NewsId = (string)dtResult.Rows[0]["NewsId"];
                    NewsCls.belongSite = (string)dtResult.Rows[0]["belongSite"];
                    try
                    {
                        NewsCls.Subject = (string)dtResult.Rows[0]["Subject"];
                        if (UseEncypt == 1)
                        {
                            NewsCls.Subject = new RSACSPCls().Decrypt(pc, NewsCls.Subject);
                        }
                    }
                    catch { }
                    try
                    {
                        NewsCls.Source = (string)dtResult.Rows[0]["Source"];
                    }
                    catch { }
                    try
                    {
                        NewsCls.ShortContent = (string)dtResult.Rows[0]["ShortContent"];
                        if (UseEncypt == 1)
                        {
                            NewsCls.ShortContent = new RSACSPCls().Decrypt(pc, NewsCls.ShortContent);
                        }
                    }
                    catch { }
                    try
                    {
                        NewsCls.Body = (string)dtResult.Rows[0]["Body"];
                        if (UseEncypt == 1)
                        {
                            NewsCls.Body = new RSACSPCls().Decrypt(pc, NewsCls.Body);
                        }
                    }
                    catch { }
                    try
                    {
                        NewsCls.Owner = (string)dtResult.Rows[0]["Owner"];
                    }
                    catch { }
                    NewsCls.GroupNewsItemId = (string)dtResult.Rows[0]["fk_GroupNewsItem_Id"];
                    NewsCls.EntryDate = (DateTime)dtResult.Rows[0]["EntryDate"];
                    try
                    {
                        NewsCls.UploadFileName = (string)dtResult.Rows[0]["UploadFileName"];
                    }
                    catch { }
                    try
                    {
                        NewsCls.SmallImage = (string)dtResult.Rows[0]["SmallImage"];
                    }
                    catch { }



                    try
                    {
                        NewsCls.TotalCount = (int)dtResult.Rows[0]["TotalView"];
                    }
                    catch { }

                    try
                    {
                        NewsCls.EmbVideo = (string)dtResult.Rows[0]["EmbVideo"];

                    }
                    catch { NewsCls.EmbVideo = string.Empty; }


                    try
                    {
                        NewsCls.EmbAudio = (string)dtResult.Rows[0]["EmbAudio"];

                    }
                    catch { NewsCls.EmbAudio = string.Empty; }


                    try
                    {
                        NewsCls.DocumentContent = dtResult.Rows[0]["DocumentContent"].ToString().Length > 0 ? new byte[dtResult.Rows[0]["DocumentContent"].ToString().Length] : null;
                    }

                    catch { NewsCls.DocumentContent = null; }

                    return NewsCls;
                }
            }
        }


        public NewsDetailCls CreateModelIncView(string ActiveLanguage, string ShortNewsId)
        {
            if (Environments.UseXmlData == false)
            {
                string Query = " Update TableNews set TotalView=TotalView + 1 where ShortId=@ShortId";
                SqlConnection sqlConnection = new SqlConnection(Environments.PublicConnectionString);
                sqlConnection.Open();
                SqlCommand sqlCommand = sqlConnection.CreateCommand();
                sqlCommand.Parameters.Add("@ShortId", SqlDbType.VarChar);
                sqlCommand.Parameters["@ShortId"].Value = ShortNewsId;
                sqlCommand.CommandText = Query;
                sqlCommand.ExecuteNonQuery();
            }
            return CreateModel(ActiveLanguage, ShortNewsId);
        }


        public DataTable Reading()
        {
            string Query = "select * from TableNews order by EntryDate DESC";
            SqlConnection sqlConnection = new SqlConnection(Environments.PublicConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingGroupNewsItemsName(string ShortNewsId)
        {
            string Query = "select NewsId,(select GroupNewsItemName from TableGroupNewsItem where GroupNewsItemId=TableNews.fk_GroupNewsItem_Id) GroupNewsItemName from TableNews where ShortId=@ShortId ";
            SqlConnection sqlConnection = new SqlConnection(Environments.PublicConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.Parameters.Add("@ShortId", SqlDbType.VarChar);
            sqlCommand.Parameters["@ShortId"].Value = ShortNewsId;
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }



        public DataTable ReadingNewsOther(
            string ActiveLanguage,
            string GroupNewsItemId,
            string NewsId,
            int Max,
            DateTime NewsDate)
        {
            string Query = "select TOP " + Max + " NewsId,Subject=[" + ActiveLanguage + "Subject],EcryptSubject=[" + ActiveLanguage + "EcryptSubject],EntryDate,fk_GroupNews_Id,fk_GroupNewsItem_Id,WorkflowStatus,Owner,ShortId,OriginUrl,TotalView,ImageUrl,ShortContent =[" + ActiveLanguage + "ShortContent]  from TableNews where IsPublishing=1 and fk_GroupNewsItem_Id='" + GroupNewsItemId + "' and NewsId<>'" + NewsId + "' and EntryDate<='" + NewsDate.ToString("MM/dd/yyyy") + "' order by EntryDate DESC";
            SqlConnection sqlConnection = new SqlConnection(Environments.PublicConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public DataTable dtNewsRelation(
            string ActiveLanguage,
            string GroupNewsItemId,
            string NewsId,
            int Max,
            DateTime NewsDate)
        {
            string Query = " select TOP " + Max + " NewsId,Subject=[" + ActiveLanguage + "Subject],EcryptSubject=[" + ActiveLanguage + "EcryptSubject],EntryDate,fk_GroupNews_Id,fk_GroupNewsItem_Id,WorkflowStatus,Owner,ShortId,OriginUrl,TotalView,ImageUrl,ShortContent =[" + ActiveLanguage + "ShortContent]  from TableNews,TableNewsRelation where " +
                            " IsPublishing=1 and " +
                            " TableNews.NewsId = TableNewsRelation.fk_RelationNews_Id and " +
                            " TableNewsRelation.fk_SrcNews_Id = @NewsId " +
                            " order by EntryDate DESC";

            SqlConnection sqlConnection = new SqlConnection(Environments.PublicConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;

            sqlCommand.Parameters.Add("@NewsId", SqlDbType.VarChar);
            sqlCommand.Parameters["@NewsId"].Value = NewsId;

            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }




        public DataTable ReadingAttachmentsNews(string NewsId)
        {
            string Query = " select TableNewsAttachmentsDetail.*,NewsId=TableNews.NewsId,TableAttachments.FriendlyTitle, TableAttachments.EntryDate,TableAttachments.Subject,TableAttachments.UploadFileName from TableNewsAttachmentsDetail " +
                           " join TableNews on TableNews.NewsId=TableNewsAttachmentsDetail.fk_NewsId " +
                           " join TableAttachments on TableAttachments.AttachmentId=TableNewsAttachmentsDetail.fk_AttachmentId " +
                           " where fk_NewsId=@NewsId and TableNews.IsPublishing=1 " +
                           " order by TableNewsAttachmentsDetail.SortIndex";

            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@NewsId", SqlDbType.VarChar);
            sqlCommand.Parameters["@NewsId"].Value = NewsId;

            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public DataTable ReadingForService(string belongSite, string ActiveLanguage)
        {
            DataTable dtResult = new DataTable();
            dtResult.Columns.Add("Id");
            dtResult.Columns.Add("Subject");
            dtResult.Rows.Add(new object[]{
                "FC84CC7E-3F08-4f70-B385-22B9CAB894CC",ResourceBLL.GetText(new FNewsDetailWP().WebPartId,"News","Xem chi tiết tin tức")
            });

            return dtResult;
        }

        public static DataTable ReadingNews(string GroupNewsItemId)
        {
            string Query = "select * from TableNews where fk_GroupNewsItem_Id='" + GroupNewsItemId + "' and IsPublishing=1 order by ShowOnTop DESC,EntryDate DESC";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;


            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }
        public static DataTable ReadingNewsDetail(string NewsId)
        {
            string Query = "select * from TableNews where NewsId='" + NewsId + "'";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        /* dvtien added for test(Questions) */
        public string InsertAsk(SqlTransaction sqlTransaction, string AskCategoryItemId, string AskSubCategoryItemId, string AskSubject, string AskContent, string AskBy, string Email, string Phone, string Address, string WorkFlowStatus, string AskCategoryId)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string AskId = System.Guid.NewGuid().ToString();
            string Query = "Insert into TableAsk(AskId,AskCategoryItemId,AskSubCategoryItemId,AskSubject,AskContent,AskBy,Email,Phone,Address,AskTime,TotalAns,WorkFlowStatus,AskCategoryId)" +
            "values (@AskId,@AskCategoryItemId,@AskSubCategoryItemId,@AskSubject,@AskContent,@AskBy,@Email,@Phone,@Address,@AskTime,@TotalAns,@WorkFlowStatus,@AskCategoryId)";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();

            AskSubCategoryItemId = null;
            sqlCommand.Parameters.Add("@AskSubCategoryItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AskSubCategoryItemId"].Value = AskSubCategoryItemId;


            AskCategoryItemId = null;
            sqlCommand.Parameters.Add("@AskCategoryItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AskCategoryItemId"].Value = AskCategoryItemId;

            sqlCommand.Parameters.Add("@AskId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AskId"].Value = AskId;

            sqlCommand.Parameters.Add("@AskSubject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@AskSubject"].Value = AskSubject;

            sqlCommand.Parameters.Add("@AskContent", SqlDbType.NVarChar);
            sqlCommand.Parameters["@AskContent"].Value = AskContent;

            sqlCommand.Parameters.Add("@AskBy", SqlDbType.NVarChar);
            sqlCommand.Parameters["@AskBy"].Value = AskBy;

            sqlCommand.Parameters.Add("@Email", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Email"].Value = Email;

            sqlCommand.Parameters.Add("@Phone", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Phone"].Value = Phone;

            sqlCommand.Parameters.Add("@AskTime", SqlDbType.Date);
            sqlCommand.Parameters["@AskTime"].Value = System.DateTime.Now;
            int total = 5;
            sqlCommand.Parameters.Add("@TotalAns", SqlDbType.Int);
            sqlCommand.Parameters["@TotalAns"].Value = total;
            WorkFlowStatus = "stop";
            sqlCommand.Parameters.Add("@WorkFlowStatus", SqlDbType.NVarChar);
            sqlCommand.Parameters["@WorkFlowStatus"].Value = WorkFlowStatus;
            AskCategoryId = null;
            sqlCommand.Parameters.Add("@AskCategoryId", SqlDbType.NVarChar);
            sqlCommand.Parameters["@AskCategoryId"].Value = AskCategoryId;

            try
            {
                sqlCommand.ExecuteNonQuery();
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
            return AskId;
        }


    }
}
